Imports System.Data.SqlClient
Public Class frmTongHop

    Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        Dim TuNgay As Date
        Dim DenNgay As Date
        If radTheoThang.Checked Then
            TuNgay = New Date(CInt(txtNam.Text), CInt(cbxThang.Text), 1)
            DenNgay = New Date(CInt(txtNam.Text), CInt(cbxThang.Text), Date.DaysInMonth(CInt(txtNam.Text), CInt(cbxThang.Text)))
        Else
            TuNgay = dtpTuNgay.Value
            DenNgay = dtpDenNgay.Value
        End If

        Dim strPath = Application.StartupPath & "\templates\bao_cao_tong_hop.xls"
        Dim strSPath = Application.StartupPath & "\temp\BaoCaoTongHop_" & Now.ToString("ddMMyyyy_") & Now.Millisecond & ".xls"
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Try

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False

            xlWorkBook = xlApp.Workbooks.Open(strPath)
            xlWorkBook.SaveCopyAs(strSPath)
            xlWorkBook.Close()
            xlWorkBook = xlApp.Workbooks.Open(strSPath)
            xlWorkSheet = xlWorkBook.Worksheets("SHN")
            'edit the cell with new value
            xlWorkSheet.Cells(1, 1).value = aryConfig.Item("CFG01")
            xlWorkSheet.Cells(2, 1).value = xlWorkSheet.Cells(2, 1).value.ToString.Replace("{DiaChi}", aryConfig.Item("CFG02"))
            xlWorkSheet.Cells(3, 1).value = xlWorkSheet.Cells(3, 1).value.ToString.Replace("{MST}", aryConfig.Item("CFG04"))

            Dim strNgay As String = xlWorkSheet.Cells(6, 1).value.ToString.Replace("{TuNgay}", TuNgay.ToString("dd/MM/yyyy"))
            strNgay = strNgay.Replace("{DenNgay}", DenNgay.ToString("dd/MM/yyyy"))
            xlWorkSheet.Cells(6, 1).value = strNgay

            Dim strNgayXuat As String = xlWorkSheet.Cells(12, 10).value.ToString.Replace("{NgayCT}", Now.Day)
            strNgayXuat = strNgayXuat.Replace("{ThangCT}", Now.Month)
            strNgayXuat = strNgayXuat.Replace("{NamCT}", Now.Year)
            xlWorkSheet.Cells(12, 10).value = strNgayXuat

            xlWorkSheet.Cells(18, 2).value = aryConfig.Item("CFG09")
            xlWorkSheet.Cells(18, 6).value = aryConfig.Item("CFG08")
            xlWorkSheet.Cells(18, 10).value = aryConfig.Item("CFG06")

            Dim tblHangHoa As DataTable = TruyVanDanhSach("Select hh.MaHH, hh.TenHH, dv.TenDV From HangHoa hh left join DonVi dv on hh.MaDV = dv.MaDV")
            Dim i As Integer
            For i = 0 To tblHangHoa.Rows.Count - 1
                Dim row As DataRow = tblHangHoa.Rows(i)
                xlWorkSheet.Cells(10 + i, 1).value = row(0)
                xlWorkSheet.Cells(10 + i, 2).value = row(1)
                xlWorkSheet.Cells(10 + i, 3).value = row(2)

                Dim sldk As Double = 0
                Dim gtdk As Double = 0
                Dim psnsl As Double = 0
                Dim psngt As Double = 0
                Dim psxgt As Double = 0
                Dim psxsl As Double = 0
                Dim gtck As Double = 0
                Dim slck As Double = 0
                Dim tblButToan As DataTable = LayDanhSachButToan(row(0), TuNgay, DenNgay)
                If tblButToan.Rows.Count > 0 Then
                    sldk += tblButToan.Rows(0)(5)
                    gtdk += tblButToan.Rows(0)(6)
                    For j As Integer = 0 To tblButToan.Rows.Count - 1
                        Dim rowbt As DataRow = tblButToan.Rows(j)
                        If rowbt(1) = "PNK" Then
                            psnsl += rowbt(2)
                            psngt += rowbt(3) + rowbt(4)
                            If j = tblButToan.Rows.Count - 1 Then
                                slck += rowbt(5) + rowbt(2)
                                gtck += rowbt(6) + rowbt(3) + rowbt(4)
                            End If
                        Else
                            psxsl += rowbt(2)
                            psxgt += rowbt(2) * rowbt(7)
                            If j = tblButToan.Rows.Count - 1 Then
                                slck += rowbt(5) - rowbt(2)
                                gtck += rowbt(6) - (rowbt(2) * rowbt(7))
                            End If
                        End If
                    Next
                Else
                    tblButToan = LayButToanTruocDo(row(0), TuNgay)
                    If tblButToan.Rows.Count > 0 Then
                        sldk += tblButToan.Rows(0)(5) + tblButToan.Rows(0)(2)
                        gtdk += tblButToan.Rows(0)(6) + tblButToan.Rows(0)(3) + tblButToan.Rows(0)(4)
                        slck += tblButToan.Rows(0)(5) + tblButToan.Rows(0)(2)
                        gtck += tblButToan.Rows(0)(6) + tblButToan.Rows(0)(3) + tblButToan.Rows(0)(4)
                    Else
                        tblButToan = TruyVanDanhSach("Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = '" & row(0) & "' and Thang = " & TuNgay.Month & " and Nam = " & TuNgay.Year)
                        If tblButToan.Rows.Count > 0 Then
                            sldk += tblButToan.Rows(0)(0)
                            gtdk += tblButToan.Rows(0)(1)
                            slck += tblButToan.Rows(0)(0)
                            gtck += tblButToan.Rows(0)(1)
                        End If
                    End If
                End If
                xlWorkSheet.Cells(10 + i, 4).value = sldk
                xlWorkSheet.Cells(10 + i, 5).value = gtdk
                xlWorkSheet.Cells(10 + i, 6).value = psnsl
                xlWorkSheet.Cells(10 + i, 7).value = psngt
                xlWorkSheet.Cells(10 + i, 8).value = psxsl
                xlWorkSheet.Cells(10 + i, 9).value = psxgt
                xlWorkSheet.Cells(10 + i, 10).value = slck
                xlWorkSheet.Cells(10 + i, 11).value = gtck
                If i <> tblHangHoa.Rows.Count - 1 Then
                    xlWorkSheet.Rows(11 + i).Select()
                    xlApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
                    xlWorkSheet.Rows(10).Select()
                    xlApp.Selection.Copy()
                    xlWorkSheet.Rows(11 + i).Select()
                    xlApp.ActiveSheet.Paste()
                End If
            Next
            xlWorkSheet.Cells(11 + i - 1, 4).value = "=Sum(D10:D" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 5).value = "=Sum(E10:E" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 6).value = "=Sum(F10:F" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 7).value = "=Sum(G10:G" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 8).value = "=Sum(H10:H" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 9).value = "=Sum(I10:I" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 10).value = "=Sum(J10:J" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 11).value = "=Sum(K10:K" & (11 + i - 2) & ")"
            xlWorkBook.Save()
            xlWorkSheet.Cells(4, 1).Select()
            xlApp.Selection.Copy()
            xlApp.Visible = True
            xlWorkBook.Activate()
            xlApp.DisplayAlerts = True

        Catch ex As Exception

        End Try
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Function LayDanhSachButToan(ByVal MaHH As String, ByVal TuNgay As Date, ByVal DenNgay As Date) As DataTable
        Dim strSql As String = "Select "
        strSql &= " ct.MaPhieuCT, "
        strSql &= " sc.LoaiCT, "
        strSql &= " ct.SoLuong, "
        strSql &= " case "
        strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
        strSql &= "	else ct.SoLuong * ct.DonGia "
        strSql &= " end as TienHang, "
        strSql &= " ct.ThueNK, "
        strSql &= " ct.TonKho, "
        strSql &= " ct.GiaTri, "
        strSql &= " ct.GiaVon "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " where "
        strSql &= " sc.NgayCT >= convert(DATETIME, @From, 103) "
        strSql &= " and sc.NgayCT <= convert(DATETIME, @To, 103) "
        strSql &= " and ct.MaHH = @MaHH "
        strSql &= " order by sc.NgayCT ASC, sc.LoaiCT"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@From", TuNgay.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@To", DenNgay.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@MaHH", MaHH)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

    Function LayButToanTruocDo(ByVal MaHH As String, ByVal TuNgay As Date) As DataTable
        Dim strSql As String = "Select Top 1 "
        strSql &= " ct.MaPhieuCT, "
        strSql &= " sc.LoaiCT, "
        strSql &= " ct.SoLuong, "
        strSql &= " case "
        strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
        strSql &= "	else ct.SoLuong * ct.DonGia "
        strSql &= " end as TienHang, "
        strSql &= " ct.ThueNK, "
        strSql &= " ct.TonKho, "
        strSql &= " ct.GiaTri, "
        strSql &= " ct.GiaVon "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " where "
        strSql &= " sc.NgayCT < convert(DATETIME, @From, 103) "
        strSql &= " and datepart(Month, sc.NgayCT) = @Thang "
        strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
        strSql &= " and ct.MaHH = @MaHH "
        strSql &= " order by sc.NgayCT DESC, sc.LoaiCT"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@From", TuNgay.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@MaHH", MaHH)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Thang", TuNgay.Month)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Nam", TuNgay.Year)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

End Class